package com.project.dao.imp;

import com.project.bean.*;
import com.project.dao.IStudentDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class IStudentDaoImp extends BaseDao implements IStudentDao {
    @Override
    public void add(StudentBean studentBean) {
        this.setConnection();
        try {
            ps = con.prepareCall("insert into r_student(r_stuName,r_stuSex,r_phoneNum,r_liveDate,fk_classId,fk_roomId,r_faceImg)" +
                    "values (?,?,?,?,?,?,?)");
            ps.setObject(1, studentBean.getName());
            ps.setObject(2, studentBean.getSex());
            ps.setObject(3, studentBean.getPhoneNum());
            ps.setObject(4, studentBean.getLiveDate());
            ps.setObject(5, studentBean.getClassBean().getId());
            ps.setObject(6, studentBean.getRoomBean().getId());
            ps.setObject(7, studentBean.getFaceImg());

            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public StudentBean findById(Integer id) {
        this.setConnection();
        StudentBean studentBean = null;
        try {
            ps = con.prepareCall("select * from r_room , r_student ,r_class  where pk_stuId =? and fk_roomId = pk_roomId and fk_classId = pk_classId");
            ps.setObject(1, id);

            re = ps.executeQuery();
            if (re.next()) {
                studentBean = new StudentBean();
                studentBean.setId(re.getInt("pk_stuId"));
                studentBean.setName(re.getString("r_stuName"));
                studentBean.setSex(re.getString("r_stuSex"));
                studentBean.setPhoneNum(re.getString("r_phoneNum"));
                studentBean.setLiveDate(LocalDate.parse(re.getString("r_liveDate")));


                ClassBean classBean = new ClassBean();
                classBean.setId(re.getInt("pk_classId"));
                classBean.setName(re.getString("r_className"));
                classBean.setStartDate(LocalDate.parse(re.getString("r_startDate")));
                classBean.setTeacher(re.getString("r_teacher"));
                studentBean.setClassBean(classBean);


                RoomBean roomBean = new RoomBean();
                roomBean.setId(re.getInt("pk_roomId"));
                roomBean.setAddress(re.getString("r_roomAdress"));
                roomBean.setMaxStu(re.getInt("r_maxNum"));
                roomBean.setState(re.getString("r_state"));
                roomBean.setType(re.getString("r_roomType"));


                studentBean.setRoomBean(roomBean);
                studentBean.setFaceImg(re.getString("r_faceImg"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return studentBean;
    }

    @Override
    public CutPageBean<StudentBean> findByItem(Integer pageNO, String name, String className, String room) {
        CutPageBean<StudentBean> cut = new CutPageBean<>();
        this.setConnection();
        String sql = "SELECT s.*, c.`r_className`,r.`r_roomAdress` FROM r_student s LEFT JOIN r_class c " +
                "ON s.`fk_classId`=c.`pk_classId` LEFT JOIN r_room r ON s.`fk_roomId`=r.`pk_roomId` WHERE 1=1 ";
        String dynaSql = "";
        if (name != null && name.length() != 0) {
            dynaSql += "AND s.`r_stuName` LIKE '%" + name + "%' ";
        }
        if (className != null && className.length() != 0) {
            dynaSql += "AND r_className  LIKE '%" + className + "%' ";
        }
        if (room != null) {
            dynaSql += "AND r.`r_roomAdress`  LIKE '%" + room + "%' ";
        }
        try {
            ps = con.prepareStatement(sql + dynaSql + " LIMIT ?,?");
            ps.setObject(1, (pageNO - 1) * CutPageBean.PAGESIZE);
            ps.setObject(2, CutPageBean.PAGESIZE);
            re = ps.executeQuery();
            while (re.next()) {
                cut.getList().add(this.getStudent());
            }
            ps = con.prepareStatement("SELECT count(*) c FROM r_student s LEFT JOIN r_class c " +
                    "ON s.`fk_classId`=c.`pk_classId` LEFT JOIN r_room r ON s.`fk_roomId`=r.`pk_roomId` WHERE 1=1 " + dynaSql);
            re = ps.executeQuery();
            while (re.next()) {
                cut.setCount(re.getInt("c"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return cut;
    }

    public StudentBean getStudent() {
        StudentBean student = new StudentBean();
        try {
            student.setId(re.getInt("pk_stuId"));
            student.setName(re.getString("r_stuName"));
            student.setPhoneNum(re.getString("r_phoneNum"));
            String bir = re.getString("r_liveDate");
            student.setLiveDate(bir != null ? LocalDate.parse(bir) : null);
            student.setSex(re.getString("r_stuSex"));
            student.setFaceImg(re.getString("r_faceImg"));


            ClassBean classBean = new ClassBean();
            classBean.setName(re.getString("r_className"));
            student.setClassBean(classBean);


            RoomBean room = new RoomBean();
            room.setAddress(re.getString("r_roomAdress"));
            student.setRoomBean(room);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }

    @Override
    public void delStu(Integer id) {
        this.setConnection();
        try {
            ps = con.prepareCall("delete from r_student where pk_stuId = ?");
            ps.setObject(1, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public void updateRoom(Integer id, Integer roomId) {
        this.setConnection();
        try {
            ps = con.prepareCall("update r_student set fk_roomId = ? where pk_stuId = ?");
            ps.setObject(1, roomId);
            ps.setObject(2, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public int countByClass(Integer classId) {
        int num = 0;
        this.setConnection();
        try {
            ps = con.prepareCall("select * from r_student where fk_classId = ?");
            ps.setObject(1, classId);

            re = ps.executeQuery();
            while (re.next()) {
                num++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return num;
    }

    @Override
    public int countByRoom(Integer roomId) {
        int num = 0;
        this.setConnection();
        try {
            ps = con.prepareCall("select * from r_student where fk_roomId = ?");
            ps.setObject(1, roomId);

            re = ps.executeQuery();
            while (re.next()) {
                num++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return num;
    }

    @Override
    public List<StudentBean> findByRoomId(Integer roomId) {
        List<StudentBean> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareCall("select * from r_student,r_class where fk_roomId = ? and fk_classId = pk_classId");
            ps.setObject(1, roomId);

            re = ps.executeQuery();
            while (re.next()) {
                StudentBean studentBean = new StudentBean();
                studentBean.setId(re.getInt("pk_stuId"));
                studentBean.setName(re.getString("r_stuName"));
                studentBean.setPhoneNum(re.getString("r_phoneNum"));

                ClassBean classBean = new ClassBean();
                classBean.setId(re.getInt("pk_classId"));
                classBean.setName(re.getString("r_className"));
                studentBean.setClassBean(classBean);
                list.add(studentBean);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
        return list;
    }

}